package org.firestorm.traficforecast.database;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.firestorm.traficforecast.utils.CommonUtils;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class RoadDAO {

    private final String[] wayType = {"motorway", "trunk", "primary",
        "secondary", "tertiary", "unclassified", "residential", "service"};

    private static final String[] status = {"rat cao", "kha nang cao",
        "hon cao", "it cao", "it thap", "kha nang thap", "hon thap",
        "rat thap"};

    public static void main(String[] args) {
		// Road road = new Road();
        // road.setId("2199767550");
        // road.setName("UNKNOW");
        // MyNode start = NodeDAO.getNode("2291049678");
        // MyNode finish = NodeDAO.getNode("2291049677");
        // road.setStart(start);
        // road.setFinish(finish);
        // road.setLen(CommonUtils.distance(start, finish));
        // road.setStatus(-1);
        // insertRoad(road);

        // createRoadTable();
        // createNextNodeTable();
//		readData();
        // creatNodeInRoadTable();
        // insertNodeInRoad("2291049678", "2291049677");
		
        processNextRoad();
//    	Road road = RoadDAO.getRoad("1100637212");
//    	System.out.println(road.getStatus());
    }

    public static void readData() {
        RoadDAO xml = new RoadDAO();
        File fXmlFile = new File(
                "E:\\HOC TAP\\KI 20141\\Project\\TAI LIEU\\map.osm");
        DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder dBuilder;
        try {
            dBuilder = dbFactory.newDocumentBuilder();
            Document doc = dBuilder.parse(fXmlFile);
            doc.getDocumentElement().normalize();

            NodeList ways = doc.getElementsByTagName("way");
            System.out.println(ways.getLength());
            int count = 0;
            int waysLen = ways.getLength();
            for (int i = 0; i < waysLen; i++) {
                if (xml.isRoad(ways.item(i))) {
                    Element way = (Element) ways.item(i);
                    String id = way.getAttribute("id");
                    String name = xml.getName(ways.item(i));
                    NodeList ndList = way.getElementsByTagName("nd");
                    int ndListLen = ndList.getLength();
                    ArrayList<String> indexs = new ArrayList<String>();
                    Element firt = (Element) ndList.item(0);
                    indexs.add(firt.getAttribute("ref"));

                    ArrayList<String> nodeInRoad = new ArrayList<String>();
                    String tmp = firt.getAttribute("ref");

                    for (int k = 1; k < ndListLen; k++) {
                        Element nd = (Element) ndList.item(k);
                        String ref = nd.getAttribute("ref");
                        tmp += "," + ref;

                        for (int j = 0; j < i; j++) {
                            if (xml.inRoad(nd, ways.item(j))) {
                                if (indexs.indexOf(ref) < 0) {
                                    indexs.add(ref);
                                    nodeInRoad.add(tmp);
                                    tmp = ref;
                                }
                                break;
                            }
                        }
                        for (int j = i + 1; j < waysLen; j++) {
                            if (xml.inRoad(nd, ways.item(j))) {
                                if (indexs.indexOf(ref) < 0) {
                                    indexs.add(ref);
                                    nodeInRoad.add(tmp);
                                    tmp = ref;
                                }
                                break;
                            }
                        }
                    }

                    // road here
                    int a = 0;
                    int b = 1;
                    int limit = (int) Math.round(Math.random() * 1000);
                    while (limit < 500) {
                        limit = (int) Math.round(Math.random() * 1000);
                    }
                    while (a < indexs.size() - 1) {
                        while (b <= indexs.size() - 1) {

                            int now = (int) Math.round(Math.random() * 1000);
                            while (now > limit) {
                                now = (int) Math.round(Math.random() * 1000);
                            }

                            int thenIndex = (int) Math.round(Math.random() * 7);

                            if (xml.isOneway(ways.item(i))) {
                                String idRoad = id + a + b;
                                MyNode start = NodeDAO.getNode(indexs.get(a));
                                MyNode finish = NodeDAO.getNode(indexs.get(b));
                                double len = CommonUtils
                                        .distance(start, finish);

                                Road road = new Road();
                                road.setId(idRoad);
                                road.setName(name);
                                road.setStart(start);
                                road.setFinish(finish);
                                road.setLen(len);

                                insertRoad(road, limit, now, status[thenIndex]);
                                String[] nodeInRoads = nodeInRoad.get(a).split(
                                        ",");
                                for (int h = 0; h < nodeInRoads.length; h++) {
                                    insertNodeInRoad(nodeInRoads[h], idRoad);
                                }
                                insertToNextNode(start.getId(), finish.getId());
                                System.out.print(id + a + ". " + name + ":"
                                        + indexs.get(a) + "[["
                                        + nodeInRoad.get(a) + "]]" + ", "
                                        + indexs.get(b) + " -- ");
                                a = b;
                                b++;
                                count += 1;
                            } else {
                                String idRoad1 = id + a + b;
                                String idRoad2 = id + b + a;
                                MyNode start = NodeDAO.getNode(indexs.get(a));
                                MyNode finish = NodeDAO.getNode(indexs.get(b));
                                double len = CommonUtils
                                        .distance(start, finish);

                                Road road1 = new Road();
                                road1.setId(idRoad1);
                                road1.setName(name);
                                road1.setStart(start);
                                road1.setFinish(finish);
                                road1.setLen(len);

                                Road road2 = new Road();
                                road2.setId(idRoad2);
                                road2.setName(name);
                                road2.setStart(finish);
                                road2.setFinish(start);
                                road2.setLen(len);

                                insertRoad(road1, limit, now, status[thenIndex]);
                                insertToNextNode(start.getId(), finish.getId());
                                insertToNextNode(finish.getId(), start.getId());

                                now = (int) Math.round(Math.random() * 1000);
                                while (now > limit) {
                                    now = (int) Math
                                            .round(Math.random() * 1000);
                                }

                                thenIndex = (int) Math.round(Math.random() * 8) - 1;
                                if (thenIndex < 0) {
                                    thenIndex = 0;
                                }
                                if (thenIndex > 7) {
                                    thenIndex = 7;
                                }
                                insertRoad(road2, limit, now, status[thenIndex]);
                                String[] nodeInRoads = nodeInRoad.get(a).split(
                                        ",");
                                for (int h = 0; h < nodeInRoads.length; h++) {
                                    insertNodeInRoad(nodeInRoads[h], idRoad1);
                                    insertNodeInRoad(nodeInRoads[h], idRoad2);
                                }
                                System.out.print(id + a + ". " + name + ":"
                                        + indexs.get(a) + "[["
                                        + nodeInRoad.get(a) + "]]" + ", "
                                        + indexs.get(b) + " -- " + id + a + a
                                        + ". " + name + ":" + indexs.get(b)
                                        + "[[" + nodeInRoad.get(a) + "]]"
                                        + ", " + indexs.get(a) + " -- ");
                                a = b;
                                b++;
                                count += 2;
                            }
                        }
                    }
                    System.out.println();
                    System.out.println("Number of road: " + count);
                }
            }

        } catch (ParserConfigurationException e) {
            e.printStackTrace();
        } catch (SAXException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private boolean isRoad(String type) {
        for (int i = 0; i < wayType.length; i++) {
            if (type.equals(wayType[i])) {
                return true;
            }
        }
        return false;
    }

    private boolean isRoad(Node way) {
        Element e = (Element) way;
        NodeList tagList = e.getElementsByTagName("tag");
        for (int i = 0; i < tagList.getLength(); i++) {
            Node tag = tagList.item(i);
            Element eTag = (Element) tag;
            if ("highway".equals(eTag.getAttribute("k"))) {
                if (isRoad(eTag.getAttribute("v"))) {
                    return true;
                }
            }
        }
        return false;
    }

    private String getName(Node way) {
        Element e = (Element) way;
        NodeList tagList = e.getElementsByTagName("tag");

        for (int i = 0; i < tagList.getLength(); i++) {
            Node tag = tagList.item(i);
            Element eTag = (Element) tag;
            if ("name".equals(eTag.getAttribute("k"))) {
                return eTag.getAttribute("v");
            }
        }
        return "unknow";
    }

    private boolean isOneway(Node way) {
        Element e = (Element) way;
        NodeList tagList = e.getElementsByTagName("tag");

        for (int i = 0; i < tagList.getLength(); i++) {
            Node tag = tagList.item(i);
            Element eTag = (Element) tag;
            if ("oneway".equals(eTag.getAttribute("k"))) {
                if ("yes".equals(eTag.getAttribute("v"))) {
                    return true;
                }
            }
        }
        return false;
    }

    private boolean inRoad(Element ndTag, Node wayNode) {
        Element way = (Element) wayNode;
        NodeList ndList = way.getElementsByTagName("nd");
        int len = ndList.getLength();
        for (int i = 0; i < len; i++) {
            Element e = (Element) ndList.item(i);
            if (ndTag.getAttribute("ref").equals(e.getAttribute("ref"))) {
                return true;
            }
        }
        return false;
    }

    public static Road getRoad(String start, String finish) {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        Road road = null;
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement("SELECT * FROM road WHERE start = '"
                    + start + "' AND finish = '" + finish + "'");

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                road = new Road();
                road.setId(rs.getString("id"));
                road.setName(rs.getString("name"));
                road.setStart(NodeDAO.getNode(start));
                road.setFinish(NodeDAO.getNode(finish));
                road.setLen(rs.getDouble("len"));
                
                Hedge hedge = HedgeDAO.getHedge();

                int limited = rs.getInt("limited");
                int numthen = hedge.getValue(rs.getString("numthen"), limited);
                int numnow = rs.getInt("numnow");
                
                int status = 0;
                double temp = (0.8*numnow + 0.2*numthen)*1.0/limited;
                if((temp < 0.65)){
                	status = -1;
                } else if(temp < 0.85){
                	status = 0;
                }else{
                	status = 1;
                }
                road.setStatus(status);
            }
            return road;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    public static Road getRoad(String id) {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        Road road = null;
        PreparedStatement stmt = null;
        PreparedStatement stmt1 = null;
        try {
            stmt = conn.prepareStatement("SELECT * FROM road WHERE id = '"
                    + id + "'");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                road = new Road();
                road.setId(rs.getString("id"));
                road.setName(rs.getString("name"));
                road.setStart(NodeDAO.getNode(rs.getString("start"), conn));
                road.setFinish(NodeDAO.getNode(rs.getString("finish"), conn));
                road.setLen(rs.getDouble("len"));

        		Hedge hedge = HedgeDAO.getHedge(conn);

                int limited = rs.getInt("limited");
                int numthen = hedge.getValue(rs.getString("numthen"), limited);
                int numnow = rs.getInt("numnow");
                
                int status = 0;
                double temp = (0.8*numnow + 0.2*numthen)*1.0/limited;
                if((temp < 0.65)){
                	status = -1;
                } else if(temp < 0.85){
                	status = 0;
                }else{
                	status = 1;
                }
                road.setStatus(status);
                
            }
            ArrayList<String> nextRoad = new ArrayList<String>();
            stmt1 = conn.prepareStatement("SELECT nextroad FROM nextroad WHERE road = '"
                    + id + "'");
            rs = stmt1.executeQuery();
            while (rs.next()) {
                String tmp = rs.getString("nextroad");
                nextRoad.add(tmp);
            }
            road.setNextRoad(nextRoad);
            return road;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            MySQLConnect.closeConnection(conn, stmt);
            MySQLConnect.closeConnection(conn, stmt1);
        }
    }

    public static Road getRoadbyName(String name) {
    	MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        Road road = null;
        PreparedStatement stmt = null;
        PreparedStatement stmt1 = null;
        try {
            stmt = conn.prepareStatement("SELECT * FROM road WHERE name LIKE '"
                    + name + "%'");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                road = new Road();
                road.setId(rs.getString("id"));
                road.setName(rs.getString("name"));
                road.setStart(NodeDAO.getNode(rs.getString("start"), conn));
                road.setFinish(NodeDAO.getNode(rs.getString("finish"), conn));
                road.setLen(rs.getDouble("len"));

        		Hedge hedge = HedgeDAO.getHedge(conn);

                int limited = rs.getInt("limited");
                int numthen = hedge.getValue(rs.getString("numthen"), limited);
                int numnow = rs.getInt("numnow");
                
                int status = 0;
                double temp = (0.8*numnow + 0.2*numthen)*1.0/limited;
                if((temp < 0.65)){
                	status = -1;
                } else if(temp < 0.85){
                	status = 0;
                }else{
                	status = 1;
                }
                road.setStatus(status);
                
            }
            ArrayList<String> nextRoad = new ArrayList<String>();
            stmt1 = conn.prepareStatement("SELECT nextroad FROM nextroad WHERE road = '"
                    + road.getId() + "'");
            rs = stmt1.executeQuery();
            while (rs.next()) {
                String tmp = rs.getString("nextroad");
                nextRoad.add(tmp);
            }
            road.setNextRoad(nextRoad);
            return road;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            MySQLConnect.closeConnection(conn, stmt);
            MySQLConnect.closeConnection(conn, stmt1);
        }
    }
    
    public static ArrayList<Road> getRoadStatus(String idNode) {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        Road road = null;
        PreparedStatement stmt = null;
        ArrayList<Road> listRoad = new ArrayList<Road>();
        try {
            stmt = conn.prepareStatement("SELECT * FROM road WHERE id IN (SELECT road FROM nodeinroad WHERE node = '" + idNode + "')");

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                String ids = rs.getString("id");
                String name = rs.getString("name");
                MyNode startNode = NodeDAO.getNode(rs.getString("start"));
                MyNode finishNode = NodeDAO.getNode(rs.getString("finish"));
                double len = rs.getDouble("len");
                Hedge h = new Hedge();
                int status = h.getValue(rs.getString("numthen"), rs.getInt("limited"));

                road = new Road();
                road.setId(ids);
                road.setName(name);
                road.setStart(startNode);
                road.setFinish(finishNode);
                road.setLen(len);
                road.setStatus(status);

                listRoad.add(road);
            }
            return listRoad;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    @SuppressWarnings("unused")
    private static void createRoadTable() {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn
                    .prepareStatement("CREATE TABLE road(id VARCHAR(255), name VARCHAR(255), start VARCHAR(255), finish VARCHAR(255), len DOUBLE, limited INTEGER, numnow INTEGER, numthen VARCHAR(255)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci");
            stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    public static void insertRoad(Road road, int limit, int now, String then) {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn
                    .prepareStatement("INSERT INTO road(id, name, start, finish, len, limited, numnow, numthen) VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
            stmt.setString(1, road.getId());
            stmt.setString(2, road.getName());
            stmt.setString(3, road.getStart().getId());
            stmt.setString(4, road.getFinish().getId());
            stmt.setDouble(5, road.getLen());
            stmt.setInt(6, limit);
            stmt.setInt(7, now);
            stmt.setString(8, then);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    @SuppressWarnings("unused")
    private static void creatNodeInRoadTable() {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn
                    .prepareStatement("CREATE TABLE nodeinroad(node VARCHAR(255), road VARCHAR(255)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci");
            stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    private static void insertNodeInRoad(String idNode, String idRoad) {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn
                    .prepareStatement("INSERT INTO nodeinroad(node, road) VALUES(?, ?)");
            stmt.setString(1, idNode);
            stmt.setString(2, idRoad);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    @SuppressWarnings("unused")
	private static void createNextNodeTable() {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn
                    .prepareStatement("CREATE TABLE nextnode(node VARCHAR(255), nextnode VARCHAR(255)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci");
            stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    private static void insertToNextNode(String node, String nextnode) {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn
                    .prepareStatement("INSERT INTO nextnode(node, nextnode) VALUES(?, ?)");
            stmt.setString(1, node);
            stmt.setString(2, nextnode);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    @SuppressWarnings("unused")
	private static void createNextRoadTable() {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn
                    .prepareStatement("CREATE TABLE nextroad(road VARCHAR(255), nextroad VARCHAR(255)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci");
            stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    private static void insertToNextRoad(String road, String nextRoad) {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn
                    .prepareStatement("INSERT INTO nextroad(road, nextroad) VALUES(?, ?)");
            stmt.setString(1, road);
            stmt.setString(2, nextRoad);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    private static void processNextRoad() {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;

        int numRoad = 0;
        ArrayList<String> listID = new ArrayList<String>();
        ArrayList<Road> listRoad = new ArrayList<Road>();
        try {
            stmt = conn.prepareStatement("SELECT id FROM road");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                listID.add(rs.getString("id"));
            }
            numRoad = listID.size();
            for (int i = 0; i < numRoad; i++) {
                Road road = getRoad(listID.get(i));
                listRoad.add(road);
            }
            for (int i = 0; i < numRoad; i++) {
                String roadFinish = listRoad.get(i).getFinish().getId();
                String idRoad = listRoad.get(i).getId();
                for (int j = 0; j < numRoad; j++) {
                    String nextRoadStart = listRoad.get(j).getStart().getId();
                    String idNextRoad = listRoad.get(j).getId();
                    if (roadFinish.equals(nextRoadStart)) {
                        insertToNextRoad(idRoad, idNextRoad);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }
}
